package com.hua.store.dao.impl;

import com.hua.store.dao.OrderDao;
import com.hua.store.domin.*;
import com.hua.store.utils.JDBCUtils;
import org.apache.commons.beanutils.BeanUtils;
import org.apache.commons.beanutils.ConvertUtils;
import org.apache.commons.beanutils.converters.DateConverter;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.lang.reflect.InvocationTargetException;
import java.sql.SQLException;
import java.util.List;
import java.util.Map;

public class OrderDaoImpl implements OrderDao {
    @Override
    public void saveOrder(Orders orders) throws Exception {
        String sql = "INSERT INTO orders VALUES(?,?,?,?,?,?,?,?)";
        QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
        Object[] params = {
                orders.getOid(),
                orders.getOrdertime(),
                orders.getTotal(),
                orders.getState(),
                orders.getAddress(),
                orders.getName(),
                orders.getTelephone(),
                orders.getUser().getUid()
        };
        queryRunner.update(sql, params);
    }

    @Override
    public void saveOrderItem(OrderItem orderItem) throws Exception {
        String sql = "INSERT INTO orderitem VALUES(?,?,?,?,?)";
        QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
        Object[] params = {
                orderItem.getItemid(),
                orderItem.getQuantity(),
                orderItem.getTotal(),
                orderItem.getProduct().getPid(),
                orderItem.getOrder().getOid()
        };
        queryRunner.update(sql, params);
    }

    @Override
    public int findTotalRecordsByUid(User user) throws Exception {
        String sql = "SELECT COUNT(*) FROM orders where uid = ?";
        QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
        Long num = (Long) queryRunner.query(sql, new ScalarHandler(), user.getUid());
        return num.intValue();
    }

    @Override
    public List<Orders> findOrdersByUidWithPage(User user, PageModel pm) throws Exception {
        String sql = "SELECT * FROM orders WHERE uid = ? LIMIT ? , ?";
        QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
        List<Orders> list = queryRunner.query(sql, new BeanListHandler<Orders>(Orders.class), user.getUid(), pm.getStartIndex(), pm.getPageSize());
        for (Orders orders : list) {
            sql = "SELECT * FROM orderitem o ,product p WHERE o.pid=p.pid AND oid=?";
            List<Map<String, Object>> list02 = queryRunner.query(sql, new MapListHandler(), orders.getOid());
            for (Map<String, Object> map : list02) {
                Product product = new Product();
                OrderItem orderItem = new OrderItem();

                // 由于BeanUtils将字符串"1992-3-3"向user对象的setBithday();方法传递参数有问题,手动向BeanUtils注册一个时间类型转换器
                // 1_创建时间类型的转换器
                DateConverter dt = new DateConverter();
                // 2_设置转换的格式
                dt.setPattern("yyyy-MM-dd");
                // 3_注册转换器
                ConvertUtils.register(dt, java.util.Date.class);

                BeanUtils.populate(product, map);
                BeanUtils.populate(orderItem, map);

                orderItem.setProduct(product);
                orders.getList().add(orderItem);
            }
        }
        return list;
    }

    @Override
    public Orders findOrderByOid(String oid) throws SQLException, InvocationTargetException, IllegalAccessException {
        String sql = "SELECT * FROM orders WHERE oid=?";
        QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
        Orders orders = queryRunner.query(sql, new BeanHandler<Orders>(Orders.class), oid);
        sql = "SELECT * FROM orderitem o, product p WHERE o.pid = p.pid AND oid=?";

        //这里查询到的是笛卡尔积，来自orderitem和product这两个表
        List<Map<String, Object>> list = queryRunner.query(sql, new MapListHandler(), oid);
        for (Map<String, Object> map : list) {
            Product product = new Product();
            OrderItem orderItem = new OrderItem();

            // 由于BeanUtils将字符串"1992-3-3"向user对象的setBithday();方法传递参数有问题,手动向BeanUtils注册一个时间类型转换器
            // 1_创建时间类型的转换器
            DateConverter dt = new DateConverter();
            // 2_设置转换的格式
            dt.setPattern("yyyy-MM-dd");
            // 3_注册转换器
            ConvertUtils.register(dt, java.util.Date.class);
            BeanUtils.populate(product, map);
            BeanUtils.populate(orderItem, map);

            orders.getList().add(orderItem);
            orderItem.setProduct(product);
        }
        return orders;
    }

    @Override
    public void updateOrder(Orders orders) throws SQLException {
        String sql = "update orders set state=?, address =? ,name = ?, telephone = ? where oid = ?";
        QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
        queryRunner.update(sql, orders.getState(), orders.getAddress(), orders.getName(), orders.getTelephone(), orders.getOid());
    }

    @Override
    public List<Orders> findAllOrder() throws SQLException {
        String sql = "SELECT * FROM orders";
        QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
        return queryRunner.query(sql, new BeanListHandler<Orders>(Orders.class));
    }

    @Override
    public List<Orders> findAllOrder(String state) throws SQLException {
        String sql = "SELECT * FROM orders WHERE state = ?";
        QueryRunner queryRunner = new QueryRunner(JDBCUtils.getDataSource());
        return queryRunner.query(sql, new BeanListHandler<Orders>(Orders.class), state);
    }
}
